from ai import AI
from sqlalchemy import create_engine, text
import pandas as pd
import sqlite3
from datetime import datetime

class ShopifyPostProcessor:
    def __init__(self, db_path, api_key, model):
        """
        初始化处理器
        :param db_path: SQLite数据库文件路径
        :param api_key: DeepSeek API key
        """
        self.db_path = db_path
        self.engine = create_engine(f'sqlite:///{db_path}')
        self.ai = AI(api_key, model)

    def process_posts(self):
        """处理所有未翻译的Shopify帖子"""
        try:
            # 获取未处理的帖子
            query = """
                SELECT id, title, content 
                FROM shopify_posts 
                WHERE title_cn IS NULL 
                OR content_cn IS NULL 
                OR summary IS NULL 
                OR requirement IS NULL
                order by id
            """
            
            with self.engine.connect() as conn:
                posts = pd.read_sql(query, conn)
                
                for _, post in posts.iterrows():
                    self._process_single_post(post)
                    
        except Exception as e:
            print(f"处理帖子时出错: {str(e)}")

    def _process_single_post(self, post):
        """
        处理单个帖子
        :param post: 帖子数据
        """
        try:
            # 翻译标题
            title_cn = self.ai.translate(post['title'])
            
            # 翻译内容
            content_cn = self.ai.translate(post['content'])
            
            # 生成内容摘要
            summary = self.ai.summarize(post['content'])
            
            # 提取开发需求
            requirement = self.ai.extract_shopify_requirements(post['content'])
            
            # 使用 SQLite 连接更新数据库
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                update_query = """
                    UPDATE shopify_posts 
                    SET 
                        title_cn = ?,
                        content_cn = ?,
                        summary = ?,
                        requirement = ?
                    WHERE id = ?
                """
                
                cursor.execute(
                    update_query,
                    (title_cn, content_cn, summary, requirement, post['id'])
                )
                conn.commit()
            
            print(f"成功处理帖子 ID: {post['id']}")
            print(f"需求: {requirement}")
            print(f"摘要: {summary}")
            print(f"内容: {content_cn}")
            print(f"标题: {title_cn}")
            
        except Exception as e:
            print(f"处理帖子 {post['id']} 时出错: {str(e)}")

    def process_single_post_by_id(self, post_id):
        """
        处理指定ID的帖子
        :param post_id: 帖子ID
        """
        try:
            query = "SELECT id, title, content FROM shopify_posts WHERE id = ?"
            
            with sqlite3.connect(self.db_path) as conn:
                df = pd.read_sql_query(query, conn, params=(post_id,))
                
                if not df.empty:
                    self._process_single_post(df.iloc[0])
                else:
                    print(f"未找到ID为 {post_id} 的帖子")
                    
        except Exception as e:
            print(f"处理帖子 {post_id} 时出错: {str(e)}")
